﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;

namespace NativeBayes
{
    public class DB
    {
        private SQLiteConnection sql_con;
        private SQLiteCommand sql_cmd;
        //private SQLiteDataAdapter DA;
        private string db_path = "";
       
        public DB(string path)
        {
            db_path = path;
        }

        private void SetConnection()
        { 
            sql_con = new SQLiteConnection
                ("Data Source=" + db_path + ";New=False;Compress=True;");
        }
        public void ExecuteQuery(string txtQuery)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        public long GetSinceID()
        {
            long sinceID;
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = "select sinceID from info where field = 1";
            SQLiteDataReader dr = sql_cmd.ExecuteReader();
            if (dr.Read())
            {
                sinceID = dr.GetInt64(0);
                sql_con.Close();
                return sinceID;
            }
            sql_con.Close();
            return 0;
        }

        public void SetSinceID(long sinceID)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = "update info set sinceID=@ID where field = 1";
            sql_cmd.Parameters.Add(new SQLiteParameter("ID", sinceID));
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }

        public void Add(long id , string  created_at , string username , string text)
        {
            SetConnection();
            sql_con.Open();
   
            string txtSQLQuery = "insert into data (id , created_at , username , text) values (@id ,@created_at ,@username , @text)";
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtSQLQuery;
            sql_cmd.Parameters.Add(new SQLiteParameter("id", id));
            sql_cmd.Parameters.Add(new SQLiteParameter("created_at", created_at));
            sql_cmd.Parameters.Add(new SQLiteParameter("username", username));
            sql_cmd.Parameters.Add(new SQLiteParameter("text", text));
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        /*
        public void incf(string word , int cat , int count , int isContain)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            if (isContain == 1)
            {
                if (cat == 0)
                    sql_cmd.CommandText = "update fc set like =@count where feature=@feature";    //like
                else
                    sql_cmd.CommandText = "update fc set dislike =@count where feature=@feature";    //dislike
            }
            else
            {
                if (cat == 0)
                     sql_cmd.CommandText = "insert into fc (feature , like) values (@feature , @count)";    //like
                else
                    sql_cmd.CommandText = "insert into fc (feature , dislike) values (@feature , @count)";   //dislike             
            }
            sql_cmd.Parameters.Add(new SQLiteParameter("count", count));
            sql_cmd.Parameters.Add(new SQLiteParameter("feature", word));
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        */
        public void db_incf(string word, int cat, int count)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();

            if (cat == 0)
                sql_cmd.CommandText = "insert into fc (feature , like , dislike) values (@feature , @count , 0)";    //like
            else
                sql_cmd.CommandText = "insert into fc (feature , dislike , like ) values (@feature , @count , 0)";   //dislike  
            sql_cmd.Parameters.Add(new SQLiteParameter("count", count));
            sql_cmd.Parameters.Add(new SQLiteParameter("feature", word));
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }

        //将fc写入数据库
        public void Updatefc(Dictionary<string, long[]> fc)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            foreach (var item in fc)
            {
                if ( item.Value[2] == 0)
                    sql_cmd.CommandText += "update fc set like =@like,dislike=@dislike where feature=@feature;";    //like
                else
                    sql_cmd.CommandText += "insert into fc (feature , dislike , like ) values (@feature , @dislike, @like);";   //dislike  

                sql_cmd.Parameters.Add(new SQLiteParameter("like",item.Value[0]));
                //MessageBox.Show("like:" + item.Value[0].ToString());
                sql_cmd.Parameters.Add(new SQLiteParameter("dislike", item.Value[1]));
               // MessageBox.Show("dislike:" + item.Value[1].ToString());
                sql_cmd.Parameters.Add(new SQLiteParameter("feature", item.Key));
                
            }
            MessageBox.Show("写入完成！1111");
            sql_cmd.ExecuteNonQuery();
            MessageBox.Show("写入完成！");
            sql_con.Close();
        }

        //将cc写入数据库
        public void Updatecc(long[] cc)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            
            sql_cmd.CommandText = "update cc set like =@like,dislike=@dislike";    //like
            sql_cmd.Parameters.Add(new SQLiteParameter("like", cc[0]));
            sql_cmd.Parameters.Add(new SQLiteParameter("dislike", cc[1]));
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        
        //从数据库读取fc
        public Dictionary<string, long[]> Loadfc()
        {
            long[] cat = new long[3];
            Dictionary<string, long[]> fc = new Dictionary<string,long[]>();
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = "select feature,like,dislike from fc";  
            SQLiteDataReader reader = sql_cmd.ExecuteReader();
            while (reader.Read())
            {
                string word = reader.GetString(0);
                cat[0] = reader.GetInt64(1);
                cat[1] = reader.GetInt64(2);
                cat[2] = 0;
                fc.Add(word, cat);
            }
            sql_con.Close();
            return fc;
        }

        //从数据库读取cc
        public long[] Loadcc()
        {
            long[] cc = new long[2];
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = "select like,dislike from cc";  
            SQLiteDataReader reader = sql_cmd.ExecuteReader();
            while (reader.Read())
            {
                cc[0] = reader.GetInt64(0);
                cc[1] = reader.GetInt64(1);
            }
            sql_con.Close();
            return cc;
        }
         
        /*
        public void Loadfc()
        {
            //Dictionary<string, long[]> fc = new Dictionary<string, long[]>();
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText =  "select feature,like,dislike from fc";  
            SQLiteDataReader reader = sql_cmd.ExecuteReader();
            while (reader.Read())
            {
                long[] cat = new long[2];
                string word = reader.GetString(0);
                cat[0] = reader.GetInt64(1);
                cat[1] = reader.GetInt64(2);
                //fc.Add(word, cat);
                MessageBox.Show(word + " " + cat[0].ToString() + " " + cat[1]);
            }
            //return fc;
            //sql_cmd.e
        }
         */
    }
}